package com.ccjt.eip.web.commons;

import com.ccjt.eip.web.annotation.Table;
import com.ccjt.eip.web.annotation.TableField;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 关sql语句的工具类
 */
public class SqlUtil {
    /**
     * 根据实体与数据库表的对应关系，配以前缀，剔除例外的字段，返回select 到 from之间的语句
     * @param cls   实体bean
     * @param tablePrefix   字段前缀，不能为空，建议填表名
     * @param excFields 排除字段（实体类的字段名，非注解中的name）
     * @return
     */
    public static String selectPart(Class<?> cls,String tablePrefix,String... excFields){
        StringBuilder selectPart = new StringBuilder("");//组织要查询的字段
        Field[] fields = cls.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Annotation[] annotations = fields[i].getAnnotations();//获取当前字段的全部注解
            for (int j = 0; j < annotations.length; j++) {
                if(annotations[j].annotationType().equals(TableField.class)){//有表字段注解
                    boolean isExcField = false;//是否为要排除的字段,默认
                    for (int k = 0; k < excFields.length; k++) {
                        if(fields[i].getName().equals(excFields[k])){
                            isExcField = true;
                            break;
                        }
                    }
                    if(isExcField){continue;}
                    if(selectPart.toString().equals("")){
                        selectPart = selectPart.append(tablePrefix)
                                .append(".")
                                .append(((TableField)annotations[j]).name())
                                .append(" ")
                                .append(fields[i].getName());
                    }else{
                        selectPart = selectPart.append(",")
                                .append(tablePrefix)
                                .append(".")
                                .append(((TableField)annotations[j]).name())
                                .append(" ")
                                .append(fields[i].getName());
                    }
                    break;
                }
            }
        }
        return selectPart.toString();
    }

    /**
     * 返回完整的插入语句
     * @param cls   实体bean
     * @param excFields 排除字段（实体类的字段名，非注解中的name）
     * @return
     */
    public static String insertSql(Class<?> cls,String... excFields){
        String tableName = "";//表名
        Annotation[] clsAnnotations = cls.getAnnotations();
        for (int i = 0; i < clsAnnotations.length; i++) {
            if(clsAnnotations[i].annotationType().equals(Table.class)){
                tableName  = ((Table)clsAnnotations[i]).name();//对应数据库表名
            }
        }
        Field[] fields = cls.getDeclaredFields();
        StringBuilder insertPart = new StringBuilder("");//组织字段
        StringBuilder questionMarks = new StringBuilder("");//组织问号
        for (int i = 0; i < fields.length; i++) {
            Annotation[] annotations = fields[i].getDeclaredAnnotations();
            for (int j = 0; j < annotations.length; j++) {
                if(annotations[j].annotationType().equals(TableField.class)){
                    boolean isExcField = false;//是否为要排除的字段,默认否
                    for (int k = 0; k < excFields.length; k++) {
                        if(fields[i].getName().equals(excFields[k])){
                            isExcField = true;
                            break;
                        }
                    }
                    boolean increment = ((TableField)annotations[j]).increment();//是否自增
                    if(increment){//自增主键不能插入
                        break;
                    }
                    if(isExcField){continue;}
                    if(insertPart.toString().equals("")){
                        insertPart = insertPart.append(((TableField)annotations[j]).name());
                        questionMarks = questionMarks.append("?");
                    }else{
                        insertPart = insertPart.append(",").append(((TableField)annotations[j]).name());
                        questionMarks = questionMarks.append(",").append("?");
                    }
                    break;
                }
            }
        }
        StringBuilder sql = new StringBuilder("insert into ");
        sql = sql.append(tableName)
                .append(" (").append(insertPart).append(") values")
                .append(" (").append(questionMarks).append(")");
        return sql.toString();
    }

    /**
     * 返回插入语句和参数集合，插入字典的顺序按照实体类的字段从上往下进行，参数值
     * @param bean  赋过值的bean
     * @param excFields 参数集合List<Object>
     * @return
     */
    public static Map<String,Object> insertSqlWithParamsArray(Object bean,String... excFields){
        Map<String,Object> map = new HashMap<>();
        String sql = SqlUtil.insertSql(bean.getClass(),excFields);
        List<Object> params = new ArrayList<>();
        Field[] fields = bean.getClass().getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            String name = fields[i].getName();
            Annotation[] annotations = fields[i].getDeclaredAnnotations();
            for (int j = 0; j < annotations.length; j++) {
                if(annotations[j].annotationType().equals(TableField.class)){
                    if(((TableField)annotations[j]).increment()==true){//自增字段不插入
                        continue;
                    }
                    String fieldName = ((TableField)annotations[j]).name();//注解的字段名称
                    if(sql.indexOf(fieldName)!=-1){
                        String getMethodName = "get"+name.substring(0,1).toUpperCase()+name.substring(1);
                        try {
                            Method method = bean.getClass().getMethod(getMethodName);
                            try {
                                params.add(method.invoke(bean));
                            } catch (IllegalAccessException e) {
                                e.printStackTrace();
                            } catch (InvocationTargetException e) {
                                e.printStackTrace();
                            }
                        } catch (NoSuchMethodException e) {
                            e.printStackTrace();
                        }
                    }
                    break;
                }
            }
        }
        map.put("sql",sql);
        map.put("params",params);
        return map;
    }

    /**
     * 修改单表大量字段的部分语句：根据实体与数据库表的对应关系，配以前缀，剔除例外的字段，返回set 到 where之间的语句
     * @param cls   实体bean
     * @param tablePrefix   字段前缀，不能为空，建议填表名
     * @param excFields 排除字段（实体类的字段名，非注解中的name）
     * @return
     */
    public static String updatePart(Class<?> cls,String tablePrefix,String... excFields){
        StringBuilder updatePart = new StringBuilder("");//组织要查询的字段
        Field[] fields = cls.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Annotation[] annotations = fields[i].getAnnotations();//获取当前字段的全部注解
            for (int j = 0; j < annotations.length; j++) {
                if(annotations[j].annotationType().equals(TableField.class)){//有表字段注解
                    boolean isExcField = false;//是否为要排除的字段,默认否
                    for (int k = 0; k < excFields.length; k++) {
                        if(fields[i].getName().equals(excFields[k])){
                            isExcField = true;
                            break;
                        }
                    }
                    boolean increment = ((TableField)annotations[j]).increment();//是否自增
                    if(increment){//自增主键不能修改
                        break;
                    }
                    if(isExcField){continue;}
                    if(updatePart.toString().equals("")){
                        updatePart = updatePart.append(tablePrefix)
                                .append(".")
                                .append(((TableField)annotations[j]).name())
                                .append("=?");
                    }else{
                        updatePart = updatePart.append(",")
                                .append(tablePrefix)
                                .append(".")
                                .append(((TableField)annotations[j]).name())
                                .append("=?");
                    }
                    break;
                }
            }
        }
        return updatePart.toString();
    }

    /**
     * 根据id修改bean的完整sql
     * @param cls
     * @param tablePrefix
     * @param excFields
     * @return
     */
    public static String updateSql(Class<?> cls,String tablePrefix,String... excFields){
        String tableName = "";//表名
        Annotation[] clsAnnotations = cls.getAnnotations();
        for (int i = 0; i < clsAnnotations.length; i++) {
            if(clsAnnotations[i].annotationType().equals(Table.class)){
                tableName  = ((Table)clsAnnotations[i]).name();//对应数据库表名
            }
        }
        StringBuilder updateSql = new StringBuilder("update ");//组织要查询的字段
        updateSql.append(tableName).append(" ").append(tablePrefix).append(" set ");
        Field[] fields = cls.getDeclaredFields();
        StringBuilder pkCondition = new StringBuilder(" where ");
        for (int i = 0; i < fields.length; i++) {
            Annotation[] annotations = fields[i].getAnnotations();//获取当前字段的全部注解
            for (int j = 0; j < annotations.length; j++) {
                if(annotations[j].annotationType().equals(TableField.class)){//有表字段注解
                    boolean isExcField = false;//是否为要排除的字段,默认否
                    for (int k = 0; k < excFields.length; k++) {
                        if(fields[i].getName().equals(excFields[k])){
                            isExcField = true;
                            break;
                        }
                    }
                    boolean increment = ((TableField)annotations[j]).increment();//是否自增
                    if(increment){//自增主键不能修改
                        pkCondition = pkCondition.append(tablePrefix)
                                .append(".")
                                .append(((TableField)annotations[j]).name())
                                .append("=?");
                        break;
                    }
                    if(isExcField){continue;}
                    if(updateSql.toString().indexOf(tablePrefix+".")==-1){
                        updateSql = updateSql.append(tablePrefix)
                                .append(".")
                                .append(((TableField)annotations[j]).name())
                                .append("=?");
                    }else{
                        updateSql = updateSql.append(",")
                                .append(tablePrefix)
                                .append(".")
                                .append(((TableField)annotations[j]).name())
                                .append("=?");
                    }
                    break;
                }
            }
        }
        return updateSql.toString()+pkCondition.toString();
    }

    public static Map<String,Object> updateSqlWithParamsArray(Object bean,String... excFields){
        Map<String,Object> map = new HashMap<>();
        String sql = SqlUtil.updateSql(bean.getClass(),"tmpt",excFields);
        List<Object> params = new ArrayList<>();
        Field[] fields = bean.getClass().getDeclaredFields();
        Object pk = null;
        for (int i = 0; i < fields.length; i++) {
            String name = fields[i].getName();
            Annotation[] annotations = fields[i].getDeclaredAnnotations();
            for (int j = 0; j < annotations.length; j++) {
                if(annotations[j].annotationType().equals(TableField.class)){
                    if(((TableField)annotations[j]).increment()==true){//自增字段不插入
                        String getMethodName = "get"+name.substring(0,1).toUpperCase()+name.substring(1);
                        try {
                            Method method = bean.getClass().getMethod(getMethodName);
                            try {
                                pk = method.invoke(bean);//获取主键的值
                            } catch (IllegalAccessException e) {
                                e.printStackTrace();
                            } catch (InvocationTargetException e) {
                                e.printStackTrace();
                            }
                        } catch (NoSuchMethodException e) {
                            e.printStackTrace();
                        }
                        continue;
                    }
                    String fieldName = ((TableField)annotations[j]).name();//注解的字段名称
                    if(sql.indexOf(fieldName+"=?")!=-1){
                        String getMethodName = "get"+name.substring(0,1).toUpperCase()+name.substring(1);
                        try {
                            Method method = bean.getClass().getMethod(getMethodName);
                            try {
                                params.add(method.invoke(bean));
                            } catch (IllegalAccessException e) {
                                e.printStackTrace();
                            } catch (InvocationTargetException e) {
                                e.printStackTrace();
                            }
                        } catch (NoSuchMethodException e) {
                            e.printStackTrace();
                        }
                    }
                    break;
                }
            }
        }
        params.add(pk);
        map.put("sql",sql);
        map.put("params",params);
        return map;
    }
    /**
     * 屏蔽指定字符字段
     * @author zhuq
     * @date 2019/3/19
     * @param  tablePrefix  必传  （别名或 表名[不存在别名]） fieldName 屏蔽的字段
     * @return
     */
    public static String shieldDesignationData(String tablePrefix, String fieldName, String[] conditions){
        StringBuilder pkCondition = new StringBuilder("");
        for (int i = 0; i < conditions.length ;i ++) {
            pkCondition.append(" and ")
                    .append(tablePrefix)
                    .append(".")
                    .append(fieldName)
                    .append(" not like ? ");
        }
        return pkCondition.toString();
    }

}
